version 16
clear all
cd "D:\AGN 2023 RCFS"
set more off
capture log close _all
log using logs\BS.smcl, append name(mainlog)
local lognum = string(clock("$S_DATE $S_TIME","DMY hms"), "%tcCCYY_NN_DD_HH_MM_SS")
log using "logs\sublogs\BSLog_`lognum'.smcl", append name(secondarylog)
	
	******************************
	*Appendix Only Code <begin>***
	******************************
			*Appendix: multiple threshold/samples draws loop begin
			*Uncomment this code to run loops over multiple thresholds or multiple samples
			*for Appendix Table 1, Columns (1) and (2) OR (3) and (4)
			
			*	eststo clear
			*	forvalues loopnum=0/99 {
				
	******************************
	*Appendix Only Code <end>*****
	******************************
	
	use hmda_applications, clear

			keep if rat<5
			
			summ rat
			return list
			
			scalar meanrat = r(mean)
			scalar abx = ceil(meanrat*10+0.5)/10-0.05-meanrat
			
			******************************
			*Appendix Only Code <begin>***
			******************************
					*For Table A.1, Columns 5 and 6 Run:
						*scalar abx = 2.05 - meanrat
					*Should then run in the resulting hmda_data file
					*	reghdfe accept af binbounddist binbounddist_p2 binbounddist_p3, vce(cluster bank) absorb(bankyear)
					*	reghdfe originate af binbounddist binbounddist_p2 binbounddist_p3, vce(cluster bank) absorb(bankyear)
			******************************
			*Appendix Only Code <end>*****
			******************************
			
			******************************
			*Appendix Only Code <begin>***
			******************************
					*For columns three and four of Appendix Table 1 (multiple threshold draws)
					*Loop over the generation of hmda_data from hmda_applications
						*Denoted with the comment "multiple threshold/samples draws loop begin/end"
						*Uncomment relevant code at either end of the loop as well!
					*Only do either the multiple threshold loop or the multiple samples loop at any given time!
					*Store the loop number (zero indexed) in local `loopnum'
					*And uncomment this code:
					
					*	set seed 1985
					*	scalar burnlooper=0
					*	while (burnlooper<`loopnum') {
					*		scalar abx = runiform()/10
					*		local abx = abx
					*		disp "Burned: `abx'"
					*		scalar burnlooper=burnlooper+1
					*	}
					*	scalar abx = runiform()/10
					*	local abx = abx
					*	disp "Using: `abx'"
					
					*Additionally, uncomment the regressions just before the end of each loop
						*For each loop collect the average coefs and t-stats, as well as the sd of the t-stats
			******************************
			*Appendix Only Code <end>*****
			******************************
			
			replace rat=rat+abx
			
			gen mrat=round(rat,0.1)

			sort unique_id
			set seed 19851985
			gen rnum=runiform()
			
			******************************
			*Appendix Only Code <begin>***
			******************************
					*For columns one and two of Appendix Table 1 (multiple sample draws)
					*Loop over the generation of hmda_data from hmda_applications
						*Denoted with the comment "multiple threshold/samples draws loop begin/end"
						*Uncomment relevant code at either end of the loop as well!
					*Only do either the multiple threshold loop or the multiple samples loop at any given time!
					*Store the loop number (zero indexed) in local `loopnum'
					*And uncomment this code:
					
					*	drop rnum
					*	sort unique_id
					*	set seed `loopnum'1985
					*	gen rnum=runiform()
					
					*Additionally, uncomment the regressions just before the end of each loop
						*For each loop collect the average coefs and t-stats, as well as teh sd of the t-stats
			******************************
			*Appendix Only Code <end>*****
			******************************
			
			preserve
			
					keep if rnum>=0.5
					drop rnum
				save testsample, replace
			
			restore
			
			keep if rnum<0.5 
			drop rnum
	
			egen bgroup=group(mrat bank white oo year)
			by bgroup, sort: egen avgaccept=mean(accept)
			by bgroup, sort: gen cg=_n
			keep if cg==1
			
			keep bank mrat year avgaccept white oo
			
			merge 1:m bank mrat white oo year using testsample
				keep if _merge==3
				drop _merge

		encode bank, gen(n_bank)

		gen mrat2=round(rat+0.05,0.1)
		gen crat2=round((mrat2+0.1)/0.1,1)
		egen bgroup2=group(crat2 bank white oo year)
		drop crat2

		egen ntract=group(tract)
		sort unique_id
		by bank year tract (unique_id), sort: gen ryzcount=_n

		areg avgaccept, absorb(bgroup2)
		predict fullshock, residuals
		drop bgroup2 avgaccept
		
		by bank year, sort: egen active=total(fdealcount)

		rename bank s_bank
		rename n_bank bank
		
		egen myear=group(mrat mrat2 year white oo)
		by myear, sort: egen mf=mean(fullshock)
		gen af=fullshock-mf
		drop mf myear fullshock
		
		by bank year tract, sort: egen taf=total(af)
		
		by year tract, sort: egen totshock=sum(af)
		gen tsother=totshock-taf	
		drop totshock
		
		gen smalltaf=taf if ryzcount==1
		by year ntract, sort: egen compcount=total(smalltaf<.) 
		by year ntract, sort: egen compmax=max(smalltaf) 
		by year ntract, sort: egen grank=rank(smalltaf), unique 
		gen grank2=1
		replace grank2=. if (compcount-grank)!=1 
		by year ntract, sort: egen tworank=max(smalltaf*grank2) 
		gen modcompmax=compmax
		replace modcompmax =tworank if taf==compmax 
		
		gen dealgrowth=(cdealcount-pdealcount) if smalltaf<.
		by year ntract, sort: egen dealgrowthmax=max(dealgrowth) 
		by year ntract, sort: egen dgrank=rank(dealgrowth), unique
		gen dgrank2=1
		replace dgrank2=. if (compcount-dgrank)!=1
		by year ntract, sort: egen dgtworank=max(dealgrowth*dgrank2)
		gen dgmax=dealgrowthmax
		replace dgmax=dgtworank if dealgrowth==dealgrowthmax 
		gen ldgmax=log(1+dgmax)
		gen secondmax=tworank
		gen grank3=1
		replace grank3=. if (compcount-grank)!=2
		by year ntract, sort: egen threerank=max(smalltaf*grank3)
		replace secondmax=threerank if taf>=tworank
		gen firstsecondmax = modcompmax + secondmax
		drop secondmax threerank tworank grank3 dgmax dgtworank dealgrowth dealgrowthmax dgrank2 dgrank compmax grank2 grank
		
		by ryzcount year ntract (cdealcount unique_id), sort: gen drank=compcount-_n+1 if ryzcount==1
		gen modtopthree_sub = smalltaf if drank<=3 & ryzcount==1
		by year ntract, sort: egen modtopthree=total(modtopthree_sub) if ryzcount==1
			drop drank modtopthree_sub
		
		by year tract, sort: egen tractdeals=total(cdealcount*(ryzcount==1))
		gen cdealcount_noshock = ((1+cdealcount)/(exp(0.0163348*smalltaf)))-1
			replace cdealcount_noshock=0 if cdealcount_noshock<0
			replace cdealcount_noshock=. if cdealcount==0
		by year tract, sort: egen tractdeals_noshock=total(cdealcount_noshock*(ryzcount==1))
		by year tract, sort: egen hhi=total(((ryzcount==1)*cdealcount)^2)
			replace hhi=hhi-cdealcount^2
			replace hhi=hhi/((tractdeals-cdealcount)^2)
		by year tract, sort: egen hhi_noshock=total(((ryzcount==1)*cdealcount_noshock)^2)
			replace hhi_noshock=hhi_noshock-cdealcount_noshock^2
			replace hhi_noshock=hhi_noshock/((tractdeals_noshock-cdealcount_noshock)^2)
		gen hhi_diff=hhi-hhi_noshock
		drop hhi hhi_noshock tractdeals_noshock cdealcount_noshock tractdeals
		
		
		gen str_zip = string(int(zip),"%05.0f")
		egen azip=concat(pctloanamt str_zip) if zip<.
		by bank year azip, sort: egen zfullshock=total(af)
		egen nazip=group(azip)
		by bank year azip, sort: gen zip_ryzcount=_n
		gen zsmalltaf=zfullshock if zip_ryzcount==1
		by year nazip, sort: egen zcompcount=total(zsmalltaf<.) 
		by year nazip, sort: egen zcompmax=max(zsmalltaf) 
		by year nazip, sort: egen zgrank=rank(zsmalltaf), unique 
		gen zgrank2=1
		replace zgrank2=. if (zcompcount-zgrank)!=1 
		by year nazip, sort: egen ztworank=max(zsmalltaf*zgrank2) 
		gen zipmodcompmax=zcompmax
		replace zipmodcompmax=ztworank if zfullshock==zcompmax    
		drop zip_ryzcount zsmalltaf zgrank zgrank2 ztworank azip zfullshock str_zip zcompmax
		
		gen istractlevel = 0
		replace istractlevel = 1 if ryzcount==1 & active>0 & active<. & tract~=""
		
		drop active
		
		gen binbounddist = rat-mrat+0.05-0.1*(mrat<rat)
		gen binbounddist_p2 = binbounddist*binbounddist
		gen binbounddist_p3 = binbounddist_p2*binbounddist
		
		gen long ntractyear = ntract*100 + (year - 2000)
		gen taf_fe=round(taf,0.01)*100+15
		
		astile cappcount_buckets = cappcount if istractlevel == 1, nquant(10)
		astile pappcount_buckets = pappcount if istractlevel == 1, nquant(10)
		astile pdealcount_buckets = pdealcount if istractlevel == 1, nquant(10)
		astile pvoltotal_buckets = pvoltotal if istractlevel == 1, nquant(10)
		
		egen bankyear = group(bank year)

		preserve 
		
				keep if ryzcount==1 & tract!=""
				keep year bank tract taf modcompmax ldgmax hhi_diff compcount
				
				replace year = year + 1
				rename taf ptaf
				rename modcompmax pmodcompmax
				rename ldgmax pldgmax
				rename hhi_diff phhi_diff
				rename compcount pcompcount
				
			save prevcompmeasures, replace
		
		restore
		
		drop ryzcount 
			
		merge m:1 year bank tract using prevcompmeasures
			drop if _merge==2
			drop _merge
		
		by year ntract istractlevel, sort: gen bankcounter=_n if istractlevel==1
		by year ntract, sort: egen den=total(cvoltotal) if istractlevel==1
		gen dealshare = cvoltotal/den
		
		******************************
		*Appendix Only Code <begin>***
		******************************
				preserve
				
					by year bank white oo mrat mrat2, sort: gen hul=_n
					by year bank white oo mrat mrat2, sort: egen cbinapp=count(outcome)
					by year bank white oo, sort: egen cmodelapp=count(outcome)
						keep if hul==1
					
					gen bindensity=cbinapp/cmodelapp
					
					by mrat mrat2, sort: egen densityrankinDTIbin=rank(bindensity), track
					by mrat mrat2, sort: egen densitiesinDTIbin=count(bindensity)
					
					gen pctile_densities = floor(100*(densityrankinDTIbin-1)/densitiesinDTIbin+1)
					gen popularbin = 0
						replace popularbin=1 if pctile_densities>=75
					
					keep mrat mrat2 year bank white oo popularbin
						
						compress
					save popularbins, replace
					
				restore
				
				merge m:1 year white oo mrat bank mrat2 using popularbins
					drop if _merge==2
					drop _merge
					
				replace popularbin = 0 if popularbin==.
				
				by bank year tract, sort: egen appinpopularbin=sum(popularbin) if istractlevel==1
				
				gen popularappfraction = appinpopularbin/cappcount if istractlevel==1
				gen unpopular_af = 0
					replace unpopular_af = af if popularbin==0
				
				by bank year tract, sort: egen unpopular_taf=total(unpopular_af)
				
				drop appinpopularbin unpopular_af
				
				gen byte app_010 = 0
					replace app_010 = 1 if abs(af)>.01
					
				gen byte app_025 = 0
					replace app_025 = 1 if abs(af)>.025
						
				by bank tract year, sort: egen app_010_frac=mean(app_010)
				by bank tract year, sort: egen app_025_frac=mean(app_025)
				
				gen high_app_010_frac = 0 if istractlevel==1
				gen high_app_025_frac = 0 if istractlevel==1
				
				replace high_app_010_frac = 1 if istractlevel==1 & app_010_frac>.5
				replace high_app_025_frac = 1 if istractlevel==1 & app_025_frac>.5

				drop app_010_frac app_025_frac
					
				preserve
				
					keep if originated
					
					collapse (sum) loanamt, by(bank year)

					gen byte aboveonebill = 0

					replace aboveonebill = 1 if loanamt>=1000000
						
						compress
					save originatorsaboveonebill, replace
					
				restore
				
				merge m:1 bank year using originatorsaboveonebill
					drop if _merge==2
					drop _merge
					
				rename bank n_bank
				rename s_bank bank
					merge m:1 bank year using RetailBankYears
						drop if _merge==2
						drop _merge
				rename bank s_bank
				rename n_bank bank
				
				by year ntract, sort: egen compcount_retail=total(smalltaf<.) if retailbankyear==1
				by year ntract, sort: egen compmax_retail=max(smalltaf) if retailbankyear==1
				by year ntract, sort: egen grank_retail=rank(smalltaf) if retailbankyear==1, unique 
				gen grank2_retail=1  if retailbankyear==1
					replace grank2_retail=. if (compcount_retail-grank_retail)!=1 & retailbankyear==1
				by year ntract, sort: egen tworank_retail=max(smalltaf*grank2_retail) if retailbankyear==1
				gen modcompmax_retail=compmax_retail if retailbankyear==1
					replace modcompmax_retail=tworank_retail if taf==compmax_retail & retailbankyear==1
					drop compcount_retail compmax_retail grank_retail grank2_retail tworank_retail
				
				gen nonbank=0
					replace nonbank=1 if substr(substr(unique_id,strpos(unique_id,"-")+1,strlen(unique_id)-strpos(unique_id,"-")),1,strpos(substr(unique_id,strpos(unique_id,"-")+1,strlen(unique_id)-strpos(unique_id,"-")),"-")-1)=="7"
				
				by year ntract, sort: egen compcount_bank=total(smalltaf<.) if nonbank==0
				by year ntract, sort: egen compmax_bank=max(smalltaf) if nonbank==0
				by year ntract, sort: egen grank_bank=rank(smalltaf) if nonbank==0, unique 
				gen grank2_bank=1 if nonbank==0
					replace grank2_bank=. if (compcount_bank-grank_bank)!=1 & nonbank==0
				by year ntract, sort: egen tworank_bank=max(smalltaf*grank2_bank) if nonbank==0
				gen modcompmax_bank=compmax_bank if nonbank==0
					replace modcompmax_bank=tworank_bank if taf==compmax_bank & nonbank==0
					drop compcount_bank compmax_bank grank_bank grank2_bank tworank_bank
					
				rename bank n_bank
				rename s_bank bank
				
				merge m:1 bank year using hmda_originatortype
					drop if _merge==2
					drop _merge
					
				rename bank s_bank
				rename n_bank bank
		******************************
		*Appendix Only Code <end>*****
		******************************
		
		drop tract smalltaf
		
		compress
	save hmda_data, replace
	
		******************************
		*Appendix Only Code <begin>***
		******************************
					*Appendix: multiple threshold/samples draws loop end
					*Uncomment this code to run loops over multiple thresholds or multiple samples
					*for Appendix Table 1, Columns (1) and (2) OR (3) and (4)
						
					*		*Column (1) or (3) depending on which loop performing
					*	eststo: reghdfe accept af binbounddist binbounddist_p2 binbounddist_p3, vce(cluster bank) absorb(bankyear)
					*		
					*		*Column (2) or (4) depending on which loop performing
					*	eststo: reghdfe originate af binbounddist binbounddist_p2 binbounddist_p3, vce(cluster bank) absorb(bankyear)
					*		
					*	}
					*	esttab using output\AGN_2023_AppendixTable01_Cols_12_OR_34.tex, r2 ar2 replace starlevels(* 0.10 ** 0.05 *** 0.01)
					
		******************************
		*Appendix Only Code <end>*****
		******************************